<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=9"/>
<meta name="generator" content="Doxygen 1.8.20"/>
<meta name="viewport" content="width=device-width, initial-scale=1"/>
<title>libxlsxwriter: Working with Outlines and Grouping</title>
<link href="tabs.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="dynsections.js"></script>
<link href="search/search.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="search/searchdata.js"></script>
<script type="text/javascript" src="search/search.js"></script>
<link href="doxygen.css" rel="stylesheet" type="text/css" />
<link href="customdoxygen.css" rel="stylesheet" type="text/css"/>
</head>
<body>
<div id="top"><!-- do not remove this div, it is closed by doxygen! -->
<div id="titlearea">
<table cellspacing="0" cellpadding="0">
 <tbody>
 <tr style="height: 56px;">
  <td id="projectalign" style="padding-left: 0.5em;">
   <div id="projectname">libxlsxwriter
   </div>
  </td>
 </tr>
 </tbody>
</table>
</div>
<!-- end header part -->
<!-- Generated by Doxygen 1.8.20 -->
<script type="text/javascript">
/* @license magnet:?xt=urn:btih:cf05388f2679ee054f2beb29a391d25f4e673ac3&amp;dn=gpl-2.0.txt GPL-v2 */
var searchBox = new SearchBox("searchBox", "search",false,'Search');
/* @license-end */
</script>
<script type="text/javascript" src="menudata.js"></script>
<script type="text/javascript" src="menu.js"></script>
<script type="text/javascript">
/* @license magnet:?xt=urn:btih:cf05388f2679ee054f2beb29a391d25f4e673ac3&amp;dn=gpl-2.0.txt GPL-v2 */
$(function() {
  initMenu('',true,false,'search.php','Search');
  $(document).ready(function() { init_search(); });
});
/* @license-end */</script>
<div id="main-nav"></div>
<!-- window showing the filter options -->
<div id="MSearchSelectWindow"
     onmouseover="return searchBox.OnSearchSelectShow()"
     onmouseout="return searchBox.OnSearchSelectHide()"
     onkeydown="return searchBox.OnSearchSelectKey(event)">
</div>

<!-- iframe showing the search results (closed by default) -->
<div id="MSearchResultsWindow">
<iframe src="javascript:void(0)" frameborder="0" 
        name="MSearchResults" id="MSearchResults">
</iframe>
</div>

</div><!-- top -->
<div class="PageDoc"><div class="header">
  <div class="headertitle">
<div class="title">Working with Outlines and Grouping </div>  </div>
</div><!--header-->
<div class="contents">
<div class="textblock"><p>Excel allows you to group rows or columns so that they can be hidden or displayed with a single mouse click. This feature is referred to as Outlines and Grouping.</p>
<p>Outlines can reduce complex data down to a few salient sub-totals or summaries. For example the following is a worksheet with three outlines.</p>
<div class="image">
<img src="outline1.png" alt=""/>
</div>
<p>Rows 2 to 11 are grouped at level 1 and rows 2 to 5 and 7 to 10 are grouped at level 2. The lines at the left hand side are called "outline level" bars and the level is shown by the small numeral above the outline.</p>
<p>Clicking the minus sign on each of the level 2 outlines will collapse and hide the data as shown below.</p>
<div class="image">
<img src="outline5.png" alt=""/>
</div>
<p>The minus sign changes to a plus sign to indicate that the data in the outline is hidden. This shows the usefulness of outlines: with 2 mouse clicks we have reduced the amount of visual data down to 2 sub-totals and a master total.</p>
<p>Finally, clicking on the minus sign on the level 1 outline will collapse the remaining rows as follows:</p>
<div class="image">
<img src="outline6.png" alt=""/>
</div>
<h1><a class="anchor" id="ww_outlines_grouping"></a>
Outlines and Grouping in libxlsxwriter</h1>
<p>Grouping in <code>libxlsxwriter</code> is achieved by setting the outline level via the <code><a class="el" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807" title="Set the properties for a row of cells.">worksheet_set_row_opt()</a></code> and <code><a class="el" href="worksheet_8h.html#a52c4c145f684c5b4dcd2ed304d1fe907" title="Set the properties for one or more columns of cells with options.">worksheet_set_column_opt()</a></code> worksheet functions:</p>
<p>Adjacent row or columns with the same outline level are grouped together into a single outline.</p>
<p>The <code>options</code> parameter is a <a class="el" href="structlxw__row__col__options.html" title="Options for rows and columns.">lxw_row_col_options</a> struct. It has the following members:</p>
<ul>
<li>&lsquo;'hidden&rsquo;<code> -</code>'level'<code> -</code>'collapsed'`</li>
</ul>
<p>Options can be set as follows, for example to set up an outline for rows:</p>
<div class="fragment"><div class="line"><span class="comment">// The option structs with the outline level set.</span></div>
<div class="line"><a class="code" href="structlxw__row__col__options.html">lxw_row_col_options</a> options1 = {.hidden = 0, .level = 2, .collapsed = 0};</div>
<div class="line"><a class="code" href="structlxw__row__col__options.html">lxw_row_col_options</a> options2 = {.hidden = 0, .level = 1, .collapsed = 0};</div>
<div class="line"> </div>
<div class="line"> </div>
<div class="line"><span class="comment">// Set the row options with the outline level.</span></div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 1,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 2,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 3,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 4,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 5,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options2);</div>
<div class="line"> </div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 6,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 7,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 8,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 9,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 10, <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options2);</div>
</div><!-- fragment --><div class="image">
<img src="outline1.png" alt=""/>
</div>
<p>Or an outline for columns:</p>
<div class="fragment"><div class="line"><a class="code" href="structlxw__row__col__options.html">lxw_row_col_options</a> options1 = {.<a class="code" href="structlxw__row__col__options.html#a0e46063d954292f4018ecb91621b7562">hidden</a> = 0, .level = 1, .collapsed = 0};</div>
<div class="line"> </div>
<div class="line"><a class="code" href="worksheet_8h.html#a52c4c145f684c5b4dcd2ed304d1fe907">worksheet_set_column_opt</a>(worksheet, <a class="code" href="utility_8h.html#a189672bfa9c380a28d8db8c40de17fe8">COLS</a>(<span class="stringliteral">&quot;B:G&quot;</span>),  5, NULL, &amp;options1);</div>
</div><!-- fragment --><div class="image">
<img src="outline8.png" alt=""/>
</div>
<p>The following example sets an outline level of 1 for rows 1 to 4 (zero-indexed) and columns B to G. The parameters <code>height</code>, <code>width</code> and <code>cell_format</code> are assigned default values:</p>
<div class="fragment"><div class="line"><a class="code" href="structlxw__row__col__options.html">lxw_row_col_options</a> options1 = {.<a class="code" href="structlxw__row__col__options.html#a0e46063d954292f4018ecb91621b7562">hidden</a> = 0, .level = 1, .collapsed = 0};</div>
<div class="line"> </div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 1,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 2,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 3,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 4,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"> </div>
<div class="line"><a class="code" href="worksheet_8h.html#a52c4c145f684c5b4dcd2ed304d1fe907">worksheet_set_column_opt</a>(worksheet, <a class="code" href="utility_8h.html#a189672bfa9c380a28d8db8c40de17fe8">COLS</a>(<span class="stringliteral">&quot;B:G&quot;</span>),  <a class="code" href="worksheet_8h.html#a3df7073ab35462516525d28975286994">LXW_DEF_COL_WIDTH</a>, NULL, &amp;options);</div>
</div><!-- fragment --><div class="image">
<img src="outline3.png" alt=""/>
</div>
<p>Rows and columns can be collapsed by setting the <code>hidden</code> member for the hidden rows/columns and setting the <code>collapsed</code> member for the row/column that has the collapsed &lsquo;&rsquo;+'` symbol:</p>
<div class="fragment"><div class="line"><a class="code" href="structlxw__row__col__options.html">lxw_row_col_options</a> options1 = {.<a class="code" href="structlxw__row__col__options.html#a0e46063d954292f4018ecb91621b7562">hidden</a> = 1, .level = 1, .collapsed = 0};</div>
<div class="line"><a class="code" href="structlxw__row__col__options.html">lxw_row_col_options</a> options2 = {.<a class="code" href="structlxw__row__col__options.html#a0e46063d954292f4018ecb91621b7562">hidden</a> = 0, .level = 0, .collapsed = 1};</div>
<div class="line"> </div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 1,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 2,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 3,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 4,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a>(worksheet, 5,  <a class="code" href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a>, NULL, &amp;options2);</div>
<div class="line"> </div>
<div class="line"><a class="code" href="worksheet_8h.html#a52c4c145f684c5b4dcd2ed304d1fe907">worksheet_set_column_opt</a>(worksheet, <a class="code" href="utility_8h.html#a189672bfa9c380a28d8db8c40de17fe8">COLS</a>(<span class="stringliteral">&quot;B:G&quot;</span>),  <a class="code" href="worksheet_8h.html#a3df7073ab35462516525d28975286994">LXW_DEF_COL_WIDTH</a>, NULL, &amp;options1);</div>
<div class="line"><a class="code" href="worksheet_8h.html#a52c4c145f684c5b4dcd2ed304d1fe907">worksheet_set_column_opt</a>(worksheet, <a class="code" href="utility_8h.html#a189672bfa9c380a28d8db8c40de17fe8">COLS</a>(<span class="stringliteral">&quot;H:H&quot;</span>),  <a class="code" href="worksheet_8h.html#a3df7073ab35462516525d28975286994">LXW_DEF_COL_WIDTH</a>, NULL, &amp;options2);</div>
</div><!-- fragment --><div class="image">
<img src="outline7.png" alt=""/>
</div>
<p>Excel allows up to 7 outline levels. Therefore the <code>level</code> parameter should be in the range <code>0 &lt;= level &lt;= 7</code>.</p>
<div class="image">
<img src="outline4.png" alt=""/>
</div>
<p>Some additional outline properties can be set via the <code><a class="el" href="worksheet_8h.html#a9efae5027e762c9a29f6afa547e4b2db" title="Set the Outline and Grouping display properties.">worksheet_outline_settings()</a></code> worksheet function.</p>
<p>Next: <a class="el" href="working_with_memory.html">Working with Memory and Performance</a> </p>
</div></div><!-- contents -->
</div><!-- PageDoc -->
<div class="ttc" id="astructlxw__row__col__options_html"><div class="ttname"><a href="structlxw__row__col__options.html">lxw_row_col_options</a></div><div class="ttdoc">Options for rows and columns.</div><div class="ttdef"><b>Definition:</b> worksheet.h:716</div></div>
<div class="ttc" id="aworksheet_8h_html_a8901b9706d1c48c28c97e95b452a927a"><div class="ttname"><a href="worksheet_8h.html#a8901b9706d1c48c28c97e95b452a927a">LXW_DEF_ROW_HEIGHT</a></div><div class="ttdeci">#define LXW_DEF_ROW_HEIGHT</div><div class="ttdef"><b>Definition:</b> worksheet.h:76</div></div>
<div class="ttc" id="aworksheet_8h_html_abbc2de45e0aa84341fb10a98778b3807"><div class="ttname"><a href="worksheet_8h.html#abbc2de45e0aa84341fb10a98778b3807">worksheet_set_row_opt</a></div><div class="ttdeci">lxw_error worksheet_set_row_opt(lxw_worksheet *worksheet, lxw_row_t row, double height, lxw_format *format, lxw_row_col_options *options)</div><div class="ttdoc">Set the properties for a row of cells.</div></div>
<div class="ttc" id="autility_8h_html_a189672bfa9c380a28d8db8c40de17fe8"><div class="ttname"><a href="utility_8h.html#a189672bfa9c380a28d8db8c40de17fe8">COLS</a></div><div class="ttdeci">#define COLS(cols)</div><div class="ttdoc">Convert an Excel A:B column range into a (col1, col2) pair.</div><div class="ttdef"><b>Definition:</b> utility.h:63</div></div>
<div class="ttc" id="aworksheet_8h_html_a52c4c145f684c5b4dcd2ed304d1fe907"><div class="ttname"><a href="worksheet_8h.html#a52c4c145f684c5b4dcd2ed304d1fe907">worksheet_set_column_opt</a></div><div class="ttdeci">lxw_error worksheet_set_column_opt(lxw_worksheet *worksheet, lxw_col_t first_col, lxw_col_t last_col, double width, lxw_format *format, lxw_row_col_options *options)</div><div class="ttdoc">Set the properties for one or more columns of cells with options.</div></div>
<div class="ttc" id="astructlxw__row__col__options_html_a0e46063d954292f4018ecb91621b7562"><div class="ttname"><a href="structlxw__row__col__options.html#a0e46063d954292f4018ecb91621b7562">lxw_row_col_options::hidden</a></div><div class="ttdeci">uint8_t hidden</div><div class="ttdef"><b>Definition:</b> worksheet.h:718</div></div>
<div class="ttc" id="aworksheet_8h_html_a3df7073ab35462516525d28975286994"><div class="ttname"><a href="worksheet_8h.html#a3df7073ab35462516525d28975286994">LXW_DEF_COL_WIDTH</a></div><div class="ttdeci">#define LXW_DEF_COL_WIDTH</div><div class="ttdef"><b>Definition:</b> worksheet.h:73</div></div>
<!-- HTML footer for doxygen 1.8.20-->
<!-- start footer part -->
<hr class="footer"/><address class="footer"><small>
Copyright 2014-2020 John McNamara.
Generated by&#160;<a href="http://www.doxygen.org/index.html"><img class="footer" src="doxygen.svg" width="104" height="31" alt="doxygen"/></a> 1.8.20
</small></address>
</body>
</html>
